﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using br.com.SistemaChamados.Model;
using br.com.SistemaChamados;
using br.com.SistemaChamados.Enum;

namespace br.com.SistemaChamados.DAO
{
    public class CallCategoryDAO
    {

        private static string SQL_INSERT_CALLCATEGORY = " INSERT INTO TBL_CALLCATEGORY (TBL_CALLCATEGORYDESCRIPTION, TBL_CALLCATEGORYMINTIMEANALIZE, TBL_CALLCATEGORYMAXTIMEANALIZE, TBL_CALLCATEGORYMINTIMETREATMENT, TBL_CALLCATEGORYMAXTIMETREATMENT) VALUES (@description, @minimumTimeAnalize, @maximumTimeAnalize, @minimumTimeTreatment, @maximumTimeTreatment) ";
        private static string SQL_UPDATE_CALLCATEGORY = " UPDATE TBL_CALLCATEGORY SET TBL_CALLCATEGORYDESCRIPTION = @description, TBL_CALLCATEGORYMINTIMEANALIZE = @minimumTimeAnalize,  TBL_CALLCATEGORYMAXTIMEANALIZE = @maximumTimeAnalize, TBL_CALLCATEGORYMINTIMETREATMENT = @minimumTimeTreatment,  TBL_CALLCATEGORYMAXTIMETREATMENT = @maximumTimeTreatment WHERE TBL_CALLCATEGORYID = @id ";
        private static string SQL_DELETE_CALLCATEGORY = " DELETE FROM TBL_CALLCATEGORY WHERE TBL_CALLCATEGORYID = @id ";
        private static string SQL_GET_CALLCATEGORIES = " SELECT * FROM TBL_CALLCATEGORY";
        private static string SQL_GET_CALLCATEGORY = " SELECT * FROM TBL_CALLCATEGORY WHERE TBL_CALLCATEGORYID = @id";

        public static CallCategory getCallCategory(CallCategory callCategory)
        {
            IDbConnection conn = null;
            IDbCommand command = null;
            IDataReader reader = null;
            CallCategory callCategoryReturn = new CallCategory();
            try
            {
                conn = Mapped.Connection();
                command = Mapped.Command(SQL_GET_CALLCATEGORY, conn);
                command.Parameters.Add(Mapped.Parameter("@id", callCategory.Id));
                reader = command.ExecuteReader(CommandBehavior.CloseConnection);
                reader.Read();
                callCategoryReturn.Description = Convert.ToString(reader["TBL_CALLCATEGORYDESCRIPTION"].ToString());
                callCategoryReturn.MinimumTimeAnalize = Convert.ToDouble(reader["TBL_CALLCATEGORYMINTIMEANALIZE"].ToString());
                callCategoryReturn.MaximumTimeAnalize = Convert.ToDouble(reader["TBL_CALLCATEGORYMAXTIMEANALIZE"].ToString());
                callCategoryReturn.MinimumTimeTreatment = Convert.ToDouble(reader["TBL_CALLCATEGORYMINTIMETREATMENT"].ToString());
                callCategoryReturn.MaximumTimeTreatment = Convert.ToDouble(reader["TBL_CALLCATEGORYMAXTIMETREATMENT"].ToString());
            }
            catch (Exception ex)
            {
                callCategoryReturn = null;
                throw ex;
            }
            finally
            {
                if (conn != null) conn.Close();
            }
            return callCategoryReturn;
        }

        public static List<CallCategory> getCallCategories()
        {
            List<CallCategory> list = new List<CallCategory>();
            IDbConnection conn = null;
            IDbCommand command = null;
            IDataReader reader = null;
            CallCategory callCategoryReturn = new CallCategory();
            try
            {
                conn = Mapped.Connection();
                command = Mapped.Command(SQL_GET_CALLCATEGORIES, conn);
                reader = command.ExecuteReader(CommandBehavior.CloseConnection);
                while (reader.Read())
                {
                    callCategoryReturn = new CallCategory();
                    callCategoryReturn.Id = Convert.ToInt32(reader["TBL_CALLCATEGORYID"].ToString());
                    callCategoryReturn.Description = Convert.ToString(reader["TBL_CALLCATEGORYDESCRIPTION"].ToString());
                    callCategoryReturn.MinimumTimeAnalize = Convert.ToDouble(reader["TBL_CALLCATEGORYMINTIMEANALIZE"].ToString());
                    callCategoryReturn.MaximumTimeAnalize = Convert.ToDouble(reader["TBL_CALLCATEGORYMAXTIMEANALIZE"].ToString());
                    callCategoryReturn.MinimumTimeTreatment = Convert.ToDouble(reader["TBL_CALLCATEGORYMINTIMETREATMENT"].ToString());
                    callCategoryReturn.MaximumTimeTreatment = Convert.ToDouble(reader["TBL_CALLCATEGORYMAXTIMETREATMENT"].ToString());
                    list.Add(callCategoryReturn);
                    callCategoryReturn = null;


                }
            }
            catch (Exception ex)
            {
                list = null;
                throw ex;
            }
            finally
            {
                if (conn != null) conn.Close();
            }
            return list;
        }

        public static EnumGenericState InsertCallCategory(CallCategory callCategory)
        {

            EnumGenericState stateTransaction = EnumGenericState.GENERIC_STATE_UNKNOW;
            IDbConnection conn = null;
            IDbCommand command = null;

            try
            {
                conn = Mapped.Connection();
                command = Mapped.Command(SQL_INSERT_CALLCATEGORY, conn);
                command.Parameters.Add(Mapped.Parameter("@description", callCategory.Description));
                command.Parameters.Add(Mapped.Parameter("@minimumTimeAnalize", callCategory.MinimumTimeAnalize));
                command.Parameters.Add(Mapped.Parameter("@maximumTimeAnalize", callCategory.MaximumTimeAnalize));
                command.Parameters.Add(Mapped.Parameter("@minimumTimeTreatment", callCategory.MinimumTimeTreatment));
                command.Parameters.Add(Mapped.Parameter("@maximumTimeTreatment", callCategory.MaximumTimeTreatment));
                command.ExecuteNonQuery();
                stateTransaction = EnumGenericState.GENERIC_STATE_SUCCESS;
            }
            catch (Exception ex)
            {
                stateTransaction = EnumGenericState.GENERIC_STATE_FAILED;
                throw ex;
            }
            finally
            {
                if (conn != null) conn.Close();
            }

            return stateTransaction;

        }

        public static EnumGenericState UpdateCallCategory(CallCategory callCategory)
        {

            EnumGenericState stateTransaction = EnumGenericState.GENERIC_STATE_UNKNOW;
            IDbConnection conn = null;
            IDbCommand command = null;

            try
            {
                conn = Mapped.Connection();
                command = Mapped.Command(SQL_UPDATE_CALLCATEGORY, conn);
                command.Parameters.Add(Mapped.Parameter("@description", callCategory.Description));
                command.Parameters.Add(Mapped.Parameter("@minimumTimeAnalize", callCategory.MinimumTimeAnalize));
                command.Parameters.Add(Mapped.Parameter("@maximumTimeAnalize", callCategory.MaximumTimeAnalize));
                command.Parameters.Add(Mapped.Parameter("@minimumTimeTreatment", callCategory.MinimumTimeTreatment));
                command.Parameters.Add(Mapped.Parameter("@maximumTimeTreatment", callCategory.MaximumTimeTreatment));
                command.Parameters.Add(Mapped.Parameter("@id", callCategory.Id));
                command.ExecuteNonQuery();
                stateTransaction = EnumGenericState.GENERIC_STATE_SUCCESS;
            }
            catch (Exception ex)
            {
                stateTransaction = EnumGenericState.GENERIC_STATE_FAILED;
                throw ex;
            }
            finally
            {
                if (conn != null) conn.Close();
            }

            return stateTransaction;

        }

        public static EnumGenericState DeleteCallCategory(CallCategory callCategory)
        {
            EnumGenericState stateTransaction = EnumGenericState.GENERIC_STATE_UNKNOW;
            IDbConnection conn = null;
            IDbCommand command = null;

            try
            {
                conn = Mapped.Connection();
                command = Mapped.Command(SQL_DELETE_CALLCATEGORY, conn);
                command.Parameters.Add(Mapped.Parameter("@id", callCategory.Id));
                command.ExecuteNonQuery();
                stateTransaction = EnumGenericState.GENERIC_STATE_SUCCESS;
            }
            catch (Exception ex)
            {
                stateTransaction = EnumGenericState.GENERIC_STATE_FAILED;
                throw ex;
            }
            finally
            {
                if (conn != null) conn.Close();
            }

            return stateTransaction;

        }

        public CallCategoryDAO()
        {
        }
    }
}